package com.mobiusws.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCountCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

@Component("exportUtil")
public class ExportUtil {
	@Resource
	private JdbcTemplate dbxJdbc;
	@Resource
	private SqlUtil sqlUtil;

	private String exportBaseDir = "/opt/GK2/portal/querydata";
	
	private String[] getColumnNames(String sql, Object[] param) {
		RowCountCallbackHandler rcch = new RowCountCallbackHandler();
		dbxJdbc.query(sql, param, rcch);
		String[] columnNames = rcch.getColumnNames();
		return columnNames;
	}
	
	private List<Object> getData(String sql, Object[] param, RowMapper rowMapper) {
		List<Object> list=dbxJdbc.query(sql, param, rowMapper);
		return list;
	}
	
	public String exportCSVDirectly(String shortFilename) {
		return shortFilename;
	}
	
	public String exportExcelDirectly(String shortFilename) {
		return shortFilename;
	}
	
	public String exportPDFDirectly(String shortFilename) {
		return shortFilename;
	}
	
	public String exportCSV(String shortFilename, String sqlId, Object[] param) {
		String sql = sqlUtil.getSqlById(sqlId);
		String[] columnNames = this.getColumnNames(sql, param);
		final int columnCount = columnNames.length;
		List<Object> list = null;
		
		// add data
		RowMapper rowMapper=new RowMapper() {
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				StringBuilder strBld = new StringBuilder();
				for (int i = 1; i <= columnCount; i++) {
					strBld.append(rs.getObject(i)).append(",");
				}
				String rowStr = strBld.substring(0, strBld.length() - 1);
				return rowStr;
			}
		};
		list=this.getData(sql, param, rowMapper);
		
		// add header
		StringBuilder headerBld = new StringBuilder();
		for (String columnName : columnNames) {
			headerBld.append(columnName).append(",");
		}
		String headerStr = headerBld.substring(0, headerBld.length() - 1);

		// generate file
		String filename = exportBaseDir + File.separator + shortFilename;
		try {
			PrintWriter pw = new PrintWriter(filename);
			pw.println(headerStr);
			for (Object row : list) {
				pw.println(row);
			}
			pw.flush();
			pw.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return filename;
	}


	private void printColumnNames(String[] columnNames) {
		StringBuilder strBld = new StringBuilder();
		for (String columnName : columnNames) {
			strBld.append(columnName).append(",");
		}
		System.out.println(strBld.substring(0, strBld.length() - 1));
	}
	
	private void printList(List<Object> list) {
		StringBuilder strBld=new StringBuilder();
		int size=list.size();
		for (int i=0; i<size; i++) {
			strBld.append(list.get(i)).append(",");
			System.out.println(strBld);
		}

	}

	public String exportExcel(String shortFilename, String sqlId, Object[] param) {
		Workbook wb = new XSSFWorkbook();
		CreationHelper createHelper = wb.getCreationHelper();
		Sheet sheet = wb.createSheet("report_data");
		String filename="";
		try {
			String sql = sqlUtil.getSqlById(sqlId);
			String[] columnNames = this.getColumnNames(sql, param);
			final int columnCount = columnNames.length;
			
			// add logo
			addExcelLogo(wb, sheet, createHelper, columnCount);
			
			// add report prefix(e.g. daily_fraud_summary_report)
			int idx=shortFilename.indexOf("20");
			String reportPrefix=shortFilename.substring(0, idx-1);
			sheet.createRow(1).createCell(0).setCellValue(reportPrefix);
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, columnCount-1));
			
			Font fBold = wb.createFont();
			fBold.setBoldweight(Font.BOLDWEIGHT_BOLD);
			CellStyle csBold = wb.createCellStyle();
			csBold.setFont(fBold);

			Font fWhite = wb.createFont();
			fWhite.setColor(IndexedColors.WHITE.getIndex());
			CellStyle csBlueBK = wb.createCellStyle();
			csBlueBK.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
			csBlueBK.setFillPattern(CellStyle.SOLID_FOREGROUND);
			csBlueBK.setFont(fWhite);
			
			// add header
			Row headerRow=sheet.createRow(2);
			for(int i=0; i<columnNames.length; i++) {
				Cell cell = headerRow.createCell(i);
				cell.setCellValue(columnNames[i]);
				cell.setCellStyle(csBlueBK);
			}

			
			// add data
			RowMapper rowMapper=new RowMapper() {
				@Override
				public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
					List<Object> innLst=new ArrayList<Object>(columnCount);
					for (int i = 1; i <= columnCount; i++) {
						innLst.add(rs.getObject(i));
					}
					return innLst;
				}
			};
			List<Object> list=this.getData(sql, param, rowMapper);
			
			int size=list.size();
			int startRow=3;
			for(int i=0; i<size; i++) {
				Object obj=list.get(i);
				List<Object> innLst=(List<Object>)obj;
				//printList(innLst);
				Row dataRow=sheet.createRow(startRow+i);
				for(int j=0; j<columnCount; j++) {
					Object colVal=innLst.get(j);
					dataRow.createCell(j).setCellValue(String.valueOf(colVal));
				} 
			}
			
			// create file
			filename = exportBaseDir + File.separator + shortFilename;
			FileOutputStream fileOut = new FileOutputStream(filename);
			wb.write(fileOut);
			fileOut.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return filename;
	}

	private void addExcelLogo(Workbook wb, Sheet sheet,
			CreationHelper createHelper, int colLen) throws Exception {
		Row row = sheet.createRow(0);
		row.setHeightInPoints(45);

		// add picture data to this workbook.
		InputStream is = new FileInputStream(
				"/opt/GK2/portal/resource/logo.png");
		byte[] bytes = IOUtils.toByteArray(is);
		int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
		is.close();

		// Create the drawing patriarch. This is the top level container for all
		// shapes.
		Drawing drawing = sheet.createDrawingPatriarch();

		// add a picture shape
		ClientAnchor anchor = createHelper.createClientAnchor();
		anchor.setAnchorType(3);
		// set top-left corner of the picture,
		// subsequent call of Picture#resize() will operate relative to it
		anchor.setCol1(2);
		anchor.setRow1(0);
		Picture pict = drawing.createPicture(anchor, pictureIdx);

		// auto-size picture relative to its top-left corner
		pict.resize();
		// merge the sheet
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colLen-1));
	}

	public static void main(String[] args) throws Exception {
		Workbook wb = new XSSFWorkbook();
		// Workbook wb = new XSSFWorkbook();
		CreationHelper createHelper = wb.getCreationHelper();
		Sheet sheet = wb.createSheet("report_data");
		Row row = sheet.createRow(0);
		row.setHeightInPoints(45);

		// add picture data to this workbook.
		InputStream is = new FileInputStream(
				"/opt/GK2/portal/resource/logo.png");
		byte[] bytes = IOUtils.toByteArray(is);
		int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
		is.close();

		// Create the drawing patriarch. This is the top level container for all
		// shapes.
		Drawing drawing = sheet.createDrawingPatriarch();

		// add a picture shape
		ClientAnchor anchor = createHelper.createClientAnchor();
		anchor.setAnchorType(3);
		// set top-left corner of the picture,
		// subsequent call of Picture#resize() will operate relative to it
		anchor.setCol1(2);
		anchor.setRow1(0);
		Picture pict = drawing.createPicture(anchor, pictureIdx);

		// auto-size picture relative to its top-left corner
		pict.resize();
		// merge the sheet
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10)); 
		
		// // Create a row and put some cells in it. Rows are 0 based.
		// Row row = sheet.createRow(0);
		// // Create a cell and put a value in it.
		// Cell cell = row.createCell(0);
		// cell.setCellValue(1);
		//
		// // Or do it on one line.
		// row.createCell(1).setCellValue(1.2);
		// row.createCell(2).setCellValue(
		// createHelper.createRichTextString("This is a string"));
		// row.createCell(3).setCellValue(true);

		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
		wb.write(fileOut);
		fileOut.close();
	}
}
